package com.yayan.xh.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

import com.yayan.xh.bean.ReviewBean;

public class ReviewDao extends ConnectionBase implements IReviewDao {

	private Connection conn = null;
	
	private QueryRunner qRunner = null;
	
	private PreparedStatement ps = null;
	
	private ResultSet rsKey = null;

	@Override
	public Integer getReviewPageCount(Map<String, String> map) throws Exception{
		int pageCount = 0;
		try {
			Map<String, Object> result = null;
			qRunner = new QueryRunner();
			String sql = "select count(*) / 10 as amount from tb_review where shop_id=?";
			conn = getConn();
			result =  qRunner.query(conn, sql, new MapHandler(), map.get("shopId"));
			pageCount = (int) Math.ceil(Double.parseDouble(result.get("amount").toString()));
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(conn, ps, rsKey);
		}
		return pageCount;
	}

	@Override
	public List<Map<String, Object>> getReviewPage(Map<String, String> map) throws Exception{
		List<Map<String, Object>> result = null;
		try {
			qRunner = new QueryRunner();
			String sql = "select a.r_id as rId, a.r_videoadd as videoAdd, a.r_detail as detail, a.r_poster as poster, a.r_clickamount as clickAmount, a.r_createdatetime as datetime, b.m_name as name from tb_review a left join tb_match b on a.m_id=b.m_id where a.shop_id=? order by a.r_createdatetime desc limit ?,10";
			conn = getConn();
			result =  qRunner.query(conn, sql, new MapListHandler(), map.get("shopId"), (Integer.parseInt(map.get("curPage").toString()) - 1) * 10);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(conn, ps, rsKey);
		}
		return result;
	}

	@Override
	public List<Map<String, Object>> getReviewById(Map<String, String> map) throws Exception {
		List<Map<String, Object>> result = null;
		try {
			qRunner = new QueryRunner();
			String sql = "select a.r_id as rId, a.r_videoadd as videoAdd, a.r_detail as detail, a.r_poster as poster, a.r_clickamount as clickAmount, a.r_createdatetime as datetime, b.m_name as name from tb_review a left join tb_match b on a.m_id=b.m_id where a.shop_id=? and a.r_id=?";
			conn = getConn();
			result =  qRunner.query(conn, sql, new MapListHandler(), map.get("shopId"), map.get("rId"));
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(conn, ps, rsKey);
		}
		return result;
	}

	@Override
	public ReviewBean getReviewByMatchId(Map<String, String> map) throws Exception {
		ReviewBean result = null;
		try {
			qRunner = new QueryRunner();
			String sql = "select * from tb_review  where shop_id=? and m_id=?";
			conn = getConn();
			result =  qRunner.query(conn, sql, new BeanHandler<ReviewBean>(ReviewBean.class), map.get("shopId"), map.get("matchId"));
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(conn, ps, rsKey);
		}
		return result;
	}

	@Override
	public boolean saveReview(Map<String, String> map) throws Exception {
		boolean f = false;
		try {
			String sql = "insert into tb_review(shop_id, m_id, r_videoadd, r_detail, r_poster, r_clickamount, r_createdatetime) values(?,?,?,?,?,0,now())";
			conn = getConn();
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(sql);
			ps.setObject(1, map.get("shopId"));
			ps.setObject(2, map.get("matchId"));
			ps.setObject(3, map.get("videoadd"));
			ps.setObject(4, map.get("detail"));
			ps.setObject(5, map.get("poster"));
			f = ps.executeUpdate() > 0 ? true : false;
			conn.commit();
			conn.setAutoCommit(true);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(conn, ps, rsKey);
		}
		return f;
	}

	@Override
	public boolean modifyReview(Map<String, String> map) throws Exception {
		boolean f = false;
		try {
			String sql = "update tb_review set r_videoadd=?, r_detail=?, r_poster=? where shop_id=? and m_id=?";
			conn = getConn();
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(sql);
			ps.setObject(1, map.get("videoadd"));
			ps.setObject(2, map.get("detail"));
			ps.setObject(3, map.get("poster"));
			ps.setObject(4, map.get("shopId"));
			ps.setObject(5, map.get("matchId"));
			f = ps.executeUpdate() > 0 ? true : false;
			conn.commit();
			conn.setAutoCommit(true);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(conn, ps, rsKey);
		}
		return f;
	}

	@Override
	public List<ReviewBean> getReviewBack(Map<String, String> map) throws Exception {
		List<ReviewBean> result = null;
		try {
			qRunner = new QueryRunner();
			String sql = "select a.*,b.m_name from tb_review a left join  tb_match b on a.m_id=b.m_id where a.shop_id=?";
			conn = getConn();
			result =  qRunner.query(conn, sql, new BeanListHandler<ReviewBean>(ReviewBean.class), map.get("shopId"));
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(conn, ps, rsKey);
		}
		return result;
	}

}
